/**
 * @description: 
 * @version: 0.0.1
 * @author: ljr
 * @Date: 2023-09-13 14:07:35
 */

import { Provide } from '@midwayjs/core';
import { ProjectRecord } from './project_records.entity';
import { InjectRepository, InjectDataSource } from '@midwayjs/sequelize';
import { Repository, Sequelize } from 'sequelize-typescript';
import { Op } from "sequelize";
import { ProOrderContact } from "./project_order_contact.entity";
import { OrdRecord } from "./orders_records.entity"

@Provide()
export class ProjectRecordModel {

  @InjectRepository(ProjectRecord)
  ProjectRecordRepository: Repository<ProjectRecord>;

  @InjectRepository(ProOrderContact)
  ProOrderContactRepository: Repository<ProOrderContact>;

  @InjectRepository(OrdRecord)
  OrdRecordRepository: Repository<OrdRecord>;

  @InjectDataSource()
  executeRawSQL: Sequelize;

  /**
   * 获取项目列表
   */
  async getProjLists(): Promise<any> {
    const sql = `SELECT id,project_name FROM project_records where is_del != 1 ORDER BY created_at DESC;`
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 根据id获取项目数据
   */
  async getProjById(id: number): Promise<any> {
    const sql = `SELECT id,project_name,project_content,project_progress,project_type,user_id,user_name,create_userid,create_username,created_at,updated_at,brand_name,
      is_import,chuanyun_object_id
      FROM project_records WHERE id = ${id};`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   *  根据Id获取项目数量
   */
  async getCountById(id: number): Promise<any> {
    const sql = `SELECT count(1) as total FROM project_records WHERE id = ${id};`
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 创建项目信息
   * @param {*} projectName    项目名称
   * @param {*} projectContent    项目内容
   * @param {*} projectProgress    项目进度
   * @param {*} projectType    项目类型
   * @param {*} userId    负责人id
   * @param {*} userName    负责人名称
   * @param {*} createUserid    创建人id
   * @param {*} createUsername    创建人名称
   * @param {*} brandName    品牌名称
   * @returns 
   */
  async insertProjectInfo(params: any): Promise<any> {
    const sql = `INSERT INTO project_records 
    SET  project_name = '${params.projectName}',
    project_content = '${params.projectContent}',
    project_progress = '${params.projectProgress}',
    project_type = '${params.projectType}',
    user_id = '${params.userId}',
    user_name = '${params.userName}',
    create_userid = ${params.createUserid},
    create_username = '${params.createUsername}',
    brand_name = '${params.brandName}',
    is_import = 0,
    created_at = now(),
    updated_at = now();`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 分页查询获取总条数
   * @param {*} searchKey   搜索信息
   * @returns 
   */
  async getTotalNum(searchKey: any): Promise<any> {
    let sql = `SELECT count(1) as num from project_records where is_del = 0;`;
    if (searchKey) {
      sql = `SELECT count(1) AS num FROM project_records AS p 
    WHERE p.is_del = 0 AND ( p.project_name LIKE '%${searchKey}%' 
        OR p.project_progress LIKE '%${searchKey}%'
        OR p.project_type LIKE '%${searchKey}%' 
        OR p.create_username LIKE '%${searchKey}%');`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results[0]
  }

  /**
   * 分页查询获取列表
   * @param {*} pageNum 
   * @param {*} pageSize 
   * @param {*} searchKey 
   * @returns 
   */
  async getDataByPage(pageNum: number, pageSize: number, searchKey: any): Promise<any> {
    const _limit = (pageNum - 1) * pageSize;
    let sql = `SELECT id, project_name,project_content, project_progress, project_type,
        user_id, user_name, create_userid, create_username,created_at, updated_at,brand_name,
        is_import,chuanyun_object_id
    FROM project_records where is_del = 0
    ORDER BY created_at DESC LIMIT ${_limit},${pageSize};`;

    if (searchKey) {
      sql = `SELECT id, project_name,project_content, project_progress, project_type,
            user_id, user_name, create_userid, create_username,created_at, updated_at,brand_name,
            is_import,chuanyun_object_id
        FROM project_records
        WHERE is_del = 0 AND ( project_name LIKE '%${searchKey}%'
            OR project_progress LIKE '%${searchKey}%' 
            OR project_type LIKE '%${searchKey}%' 
            OR create_username LIKE '%${searchKey}%') 
        ORDER BY created_at DESC LIMIT ${_limit},${pageSize};`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 根据id删除项目数据
   * @param {*} id 
   * @returns 
   */
  async delDataById(id: number): Promise<any> {
    const sql = `update project_records set is_del = 1 where id = ${id}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }


  /**
   * 修改项目信息
   * @param {*} id 
   * @returns 
   */
  async updateProjectInfo(params: any): Promise<any> {
    const sql = `update project_records set 
    project_name = '${params.projectName}',
    project_content = '${params.projectContent}',
    project_progress = '${params.projectProgress}',
    project_type = '${params.projectType}',
    user_id = '${params.userId}',
    user_name = '${params.userName}',
    brand_name = '${params.brandName}',
    updated_at = now()
    where id = ${params.id}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 氚云导入订单同时 创建项目信息
   * @param {*} project_name    项目名称
   * @param {*} chuanyun_object_id    氚云导入项目的ObjectId
   * @param {*} user_id    负责人id
   * @param {*} user_name    负责人名称
   * @param {*} brand_name    品牌名称
   * @returns 
   */
  async chuanyunInsertProject(params: any): Promise<any> {
    return await this.ProjectRecordRepository.create(
      {
        project_name: params.project_name,
        chuanyun_object_id: params.chuanyun_object_id,
        user_id: params.user_id,
        user_name: params.user_name,
        brand_name: params.brand_name,
        is_import: 1,
        created_at: new Date(),
        updated_at: new Date()
      }
    );
  }

  async getDataByPageByOrderId(pageNum: number, pageSize: number, searchKey: any, orderId: any): Promise<any> {
    const _limit = (pageNum - 1) * pageSize;
    const attributes = ['id', 'project_name', 'project_content', 'project_progress',
      'project_type', 'user_id', 'user_name', 'create_userid',
      'create_username', 'created_at', 'updated_at', 'brand_name',
      'is_import', 'chuanyun_object_id']

    // 在 UserRecord 模型中
    this.ProjectRecordRepository.belongsToMany(this.OrdRecordRepository, { through: this.ProOrderContactRepository, foreignKey: 'proj_id', otherKey: 'order_id' });

    // 在 UserRoleRecord 模型中
    this.OrdRecordRepository.belongsToMany(this.ProjectRecordRepository, { through: this.ProOrderContactRepository, foreignKey: 'order_id', otherKey: 'proj_id' });

    if (orderId) {
      const { count, rows } = await this.OrdRecordRepository.findAndCountAll({
        where: {
          is_del: 0,
          order_id: { [Op.like]: `%${orderId}%` }
        },
        attributes: ['order_id', 'order_name'],
        offset: _limit,
        limit: pageSize,
        include: [{
          model: this.ProjectRecordRepository,
          attributes: attributes,
          through: {
            attributes: [], // 不需要中间表的属性时可以这样设置
          },
          required: false // 确保是 LEFT JOIN
        }]
      })
      return {
        currentPage: pageNum,
        totalPages: Math.ceil(count / pageSize),
        totalItems: count,
        items: rows
      };
    } else {
      const { count, rows } = await this.ProjectRecordRepository.findAndCountAll({
        where: searchKey ? {
          is_del: 0,
          [Op.or]: [
            { project_name: { [Op.like]: `%${searchKey}%` } },
            { project_progress: { [Op.like]: `%${searchKey}%` } },
            { project_type: { [Op.like]: `%${searchKey}%` } },
            { create_username: { [Op.like]: `%${searchKey}%` } }
          ]
        } : { is_del: 0 },
        attributes: attributes,
        order: [['created_at', 'DESC']],
        offset: _limit,
        limit: pageSize,
        include: [{
          model: this.OrdRecordRepository,
          attributes: ['id', 'order_id', 'order_name'],
          through: {
            attributes: [], // 不需要中间表的属性时可以这样设置
          },
          required: false // 确保是 LEFT JOIN
        }]
      })
      return {
        currentPage: pageNum,
        totalPages: Math.ceil(count / pageSize),
        totalItems: count,
        items: rows
      };

    }
  }

}